package cn.duhengkui.national.poi.saxexcel;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.OutputStreamWriter;
import java.io.Writer;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.Locale;
import java.util.Map;
import java.util.UUID;

import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.openxml4j.exceptions.OpenXML4JException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.BuiltinFormats;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.xssf.model.StylesTable;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.xml.sax.Attributes;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.DefaultHandler;
import org.xml.sax.helpers.XMLReaderFactory;

import cn.duhengkui.national.poi.define.ExcelImportStatus;
import cn.duhengkui.national.poi.exception.ExcelImportException;
import cn.duhengkui.national.poi.utils.MyDataFormatter;
import cn.duhengkui.national.poi.utils.XMLEncoder;

/**   
 * <table border="1">
 *	<tr><td>包名称：</td><td>com.zhibo.tower.guizhou.ibs.utils.saxExcel</td></tr>
 *	<tr><td>类名称：</td><td>Excel2007Reader</td></tr>
 *	<tr><td>类描述：</td>excel2007读取工具类<td></td></tr>
 *	<tr><td>创建人：</td><td>杜恒奎</td></tr>
 *  <tr><td>创建时间：</td><td>2017年6月21日/上午9:01:44</td></tr> 
 *  <tr><td>版本：</td><td>1.0.0</td></tr>
 * </table>   
 *     
 */
public class Excel2007Reader extends DefaultHandler {

    private SharedStringsTable sst;
 
    private String lastContents="";
 
    @SuppressWarnings("unused")
	private boolean nextIsString;
 
    @SuppressWarnings("unused")
	private int sheetIndex = -1;
 
    private Map<Integer,String> rowMap = new LinkedHashMap<Integer,String>();
 
    private int curRow = 0;
 
    private int curCol = 0;
 
    private boolean isTElement;
 
    private RowHandler rowHandler;
 
    private String exceptionMessage;
 

    private CellDataType nextDataType = CellDataType.SSTINDEX;
 
    private final MyDataFormatter formatter = new MyDataFormatter(Locale.CHINESE);
 
    private short formatIndex;
 
    private String formatString;
    
    
    private String currentR;
 
    private StylesTable stylesTable;
    
    private boolean needReExport = false;
    
    private String exportPath;
    
    private String exportName;
    
    private int errorRowIndex = 0;
    
    private int maxCols = 0;
    
    private SpreadsheetWriter sw = null;
    
    private File tmp = null;
    
    private XSSFWorkbook wb = null;
    
    private Writer fw = null;
    
    private XSSFSheet sheet = null;
    
    private String tmpFileName = UUID.randomUUID().toString();

    private ReadResult readResult = new ReadResult();
    
    private Map<Integer,String> excelHead = new HashMap<Integer,String>();
    

	public ReadResult getReadResult() {
		return readResult;
	}

	public Excel2007Reader(){}
    
    public Excel2007Reader(boolean needReExport, String exportPath, String exportName){
    	this.needReExport = needReExport;
    	this.exportPath = exportPath;
    	this.exportName = exportName;
    }
 
    /**
    * @Title: setRowReader
    * @Description: rowHandler
    * @param rowHandler
    * @return void
    */ 
    public void setRowReader(RowHandler rowHandler)
    {
        this.rowHandler = rowHandler;
    }
 

    /**
    * @Title: process
    * @Description: 读取流操作
    * @param fileInputStream
    * @throws IOException
    * @throws OpenXML4JException
    * @throws SAXException
    * @return void
    */ 
    public void process(InputStream fileInputStream) throws IOException, OpenXML4JException, SAXException
    {
        OPCPackage pkg = OPCPackage.open(fileInputStream);
        XSSFReader xssfReader = new XSSFReader(pkg);
        stylesTable = xssfReader.getStylesTable();
        SharedStringsTable sst = xssfReader.getSharedStringsTable();
        XMLReader parser = this.fetchSheetParser(sst);
        Iterator<InputStream> sheets = xssfReader.getSheetsData();
        while (sheets.hasNext())
        {
            curRow = 0;
            sheetIndex++;
            InputStream sheet = sheets.next();
            InputSource sheetSource = new InputSource(sheet);
            parser.parse(sheetSource);
            sheet.close();
            //一个sheet页
            break;
        }
    }
 
    /**
    * @Title: fetchSheetParser
    * @Description: 获取xmlReader
    * @param sst
    * @return
    * @throws SAXException
    * @return XMLReader
    */ 
    public XMLReader fetchSheetParser(SharedStringsTable sst) throws SAXException
    {
        XMLReader parser = XMLReaderFactory.createXMLReader("com.sun.org.apache.xerces.internal.parsers.SAXParser");
        this.sst = sst;
        parser.setContentHandler(this);
        return parser;
    }
    
    /* (non-Javadoc)
     * @see org.xml.sax.helpers.DefaultHandler#startElement(java.lang.String, java.lang.String, java.lang.String, org.xml.sax.Attributes)
     */
    @Override
    public void startElement(String uri, String localName, String name,	Attributes attributes) throws SAXException
    {
    	if ("c".equals(name)) {
			String cellType = attributes.getValue("t");
			if ("s".equals(cellType)) {
				nextIsString = true;
			} else {
				nextIsString = false;
			}
			setNextDataType(attributes);
    	}
        if ("t".equals(name))
        {
            isTElement = true;
        }
        else
        {
            isTElement = false;
        }
 
        // �ÿ�
        lastContents = "";
    }
 

    enum CellDataType
    {
        BOOL, ERROR, FORMULA, INLINESTR, SSTINDEX, NUMBER, DATE, NULL
    }
 

    /**
    * @Title: setNextDataType
    * @Description: 分析后续类型
    * @param attributes
    * @return void
    */ 
    public void setNextDataType(Attributes attributes)
    {
        nextDataType = CellDataType.NUMBER;
        formatIndex = -1;
        formatString = null;
        currentR = attributes.getValue("r");
        
        String cellType = attributes.getValue("t");
        String cellStyleStr = attributes.getValue("s");
 
        if ("b".equals(cellType))
        {
            nextDataType = CellDataType.BOOL;
        }
        else if ("e".equals(cellType))
        {
            nextDataType = CellDataType.ERROR;
        }
        else if ("inlineStr".equals(cellType))
        {
            nextDataType = CellDataType.INLINESTR;
        }
        else if ("s".equals(cellType))
        {
            nextDataType = CellDataType.SSTINDEX;
        }
        else if ("str".equals(cellType))
        {
            nextDataType = CellDataType.FORMULA;
        }
 
        if (cellStyleStr != null)
        {
            int styleIndex = Integer.parseInt(cellStyleStr);
            XSSFCellStyle style = stylesTable.getStyleAt(styleIndex);
            formatIndex = style.getDataFormat();
            formatString = style.getDataFormatString();
 
            if (formatString == null)
            {
                nextDataType = CellDataType.NULL;
                formatString = BuiltinFormats.getBuiltinFormat(formatIndex);
            }
        }
    }
 

    /**
    * @Title: getDataValue
    * @Description: 类型处理，统一转字符串
    * @param value
    * @param thisStr
    * @return
    * @return String
    */ 
    public String getDataValue(String value, String thisStr)
    {
        switch (nextDataType)
        {
            case BOOL:
                char first = value.charAt(0);
                thisStr = first == '0' ? "FALSE" : "TRUE";
                break;
            case ERROR:
                thisStr = "\"ERROR:" + value.toString() + '"';
                break;
            case FORMULA:
                thisStr = '"' + value.toString() + '"';
                break;
            case INLINESTR:
                XSSFRichTextString rtsi = new XSSFRichTextString(value.toString());
 
                thisStr = rtsi.toString();
                rtsi = null;
                break;
            case SSTINDEX:
                String sstIndex = value.toString();
                try
                {
                    int idx = Integer.parseInt(sstIndex);
                     XSSFRichTextString rtss = null;
                     rtss = new XSSFRichTextString(sst.getEntryAt(idx));
                     thisStr = rtss.toString();
                    rtss = null;
                }
                catch (NumberFormatException ex)
                {
                    thisStr = value.toString();
                }
                break;
            case NUMBER:
                if (formatString != null)
                {
                    thisStr = formatter.formatRawCellContents(Double.parseDouble(value), formatIndex, formatString.replaceAll("\"", ""))
                            .trim();
                }
                else
                {
                    thisStr = value;
                }
 
                thisStr = thisStr.replace("_", "").trim();
                break;
            case DATE:
                thisStr = formatter.formatRawCellContents(Double.parseDouble(value), formatIndex, formatString.replaceAll("\"", ""));

                thisStr = thisStr.replace(" ", "T");
                break;
            default:
                thisStr = " ";
 
                break;
        }
 
        return thisStr;
    }
 
    /* (non-Javadoc)
     * @see org.xml.sax.helpers.DefaultHandler#endElement(java.lang.String, java.lang.String, java.lang.String)
     */
    @Override
    public void endElement(String uri, String localName, String name) throws SAXException
    {
        if (isTElement)
        {
            String value = lastContents.trim();
            curCol = getColNumber(currentR);
            rowMap.put(curCol, value);
            isTElement = false;
        }
        else if ("v".equals(name))
        {
            String value = this.getDataValue(lastContents.trim(), "");
            curCol = getColNumber(currentR);
            rowMap.put(curCol, value);
        }
        else
        {

            if (name.equals("row"))
            {
                try
                {
                	if(curRow == 0){
                    		for(Integer key : rowMap.keySet()){
                    				if(key > maxCols){
                    					maxCols = key;
                    				}
                    		}
                    		if(needReExport){
                    			rowMap.put(maxCols + 1, "错误原因");
                    		}
                    		excelHead.putAll(rowMap);
                    		rowHandler.headValidate(excelHead);
                    }else{
                    	ExcelImportStatus status = rowHandler.operator(curRow, rowMap,readResult);
                		if(ExcelImportStatus.UNDATABASE_ROOLBACK.equals(status)){
                			throw new ExcelImportException(rowHandler.getErrMsg());
                		}
                		else if(ExcelImportStatus.UNDATABASE_UNROOLBACK.equals(status)){
                			if(needReExport){
                				rowMap.put(maxCols+1, rowHandler.getErrMsg());
                        		if(wb == null){	
                        			wb = new XSSFWorkbook();
                        			CellStyle cellStyle = wb.createCellStyle();
                        			cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("@"));
                        			
                        			CellStyle style = wb.createCellStyle();
                        			style.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());
                        			style.setFillPattern(CellStyle.SOLID_FOREGROUND);
                        			
                        			FileOutputStream os = new FileOutputStream(tmpFileName + ".xlsx");
                        			sheet = wb.createSheet("sheet1");
                        			wb.write(os);
                        			os.close();
                        				
                					tmp = File.createTempFile("exportSheet",".xml");
                					fw = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(tmp),"UTF-8"));
                					sw = new SpreadsheetWriter(fw);
                					
                					int[] widths = new int[maxCols-1];
                					for(int i = 0; i < widths.length;i++){
                						widths[i] = 30;
                					}
                					Excel2007Writer.beginSheet(sw,widths);
                					insertErrorRow(excelHead);
                					insertErrorRow(rowMap);
                        		}else{
                        			insertErrorRow(rowMap);
                        		}
                        	}
                		}
                	}
                }
                catch (Exception e)
                {
                    exceptionMessage = e.getMessage();
                    throw new ExcelImportException(e);
/*                    e.printStackTrace();*/
                }
                rowMap.clear();
                curRow++;
                curCol = 0;
            }else if("worksheet".equals(name)){
            	if(needReExport && wb != null && sw != null){
            		try {
						sw.endSheetData();
						sw.pageMargins();
						sw.endWorksheet();
						fw.close();
					} catch (IOException e) {}
            		Map<String, File> tmpFileMap = new HashMap<String, File>();
            		tmpFileMap.put(sheet.getPackagePart().getPartName().getName().substring(1), tmp);
            		
            		/*if(!file.exists()){
            			file.createNewFile();
            		}*/
            		try {
            			
            			File templateFile = new File(tmpFileName + ".xlsx");
						OutputStream out = new FileOutputStream(exportPath + "/" + exportName + ".xlsx");
						Excel2007Writer.substitute(templateFile, tmpFileMap, out);
						out.close();
						if (templateFile.isFile()){
							templateFile.delete();
						}
						for(String entry : tmpFileMap.keySet()){
							tmpFileMap.get(entry).delete();
						}
					} catch (FileNotFoundException e) {
						throw new ExcelImportException(e);
					} catch (IOException e) {
						throw new ExcelImportException(e);
					}
            		
            		
            	}
            }
        }
    }
    
    private void insertErrorRow(Map<Integer,String> resultMap) throws IOException{
    	if(!resultMap.isEmpty()){
    		sw.insertRow(errorRowIndex);
    		errorRowIndex++;
    		for(int i = 1; i <= maxCols + 1; i++){
    			sw.createCell(i-1, resultMap.get(i) == null?null:XMLEncoder.unencode(resultMap.get(i)));
    		}
    		sw.endRow();
    	}
    }
 
    /* (non-Javadoc)
     * @see org.xml.sax.helpers.DefaultHandler#characters(char[], int, int)
     */
    @Override
    public void characters(char[] ch, int start, int length) throws SAXException
    {
        lastContents += new String(ch, start, length);
    }
 
    /**
     * @return the exceptionMessage
     */
    public String getExceptionMessage()
    {
        return exceptionMessage;
    }
    
    /**
     * @Title: getColNumber
     * @Description: 获取列位置
     * @param r
     * @return
     * @return int
    */ 
    private int getColNumber(String r){
    	String column = r.replaceAll("\\d", "");
    	int index = 0;
    	char[] chars = column.toUpperCase().toCharArray();
        for (int i = 0; i < chars.length; i++) {
                index += ((int) chars[i] - (int) 'A' + 1)
                                * (int) Math.pow(26, chars.length - i - 1);
        }
        return index;
    }
    
}